using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlServerCe;
using System.IO;
  
namespace AtCareApplication
{
    class ScannerApplication
    {
        string sourceFile = "\\meals\\data\\barcode.upl";
        int currentIndex = 0;
        int nextIndex = 0;
        int previousIndex = 0;
        int currentDataNumber = 1;
        
        public void makeConnection(/*Meals meals*/)
        {
            try
            {
                SqlCeConnection con = new SqlCeConnection("Data Source=\\meals\\meals.sdf");
                con.Open();
                SqlCeCommand comInsert = new SqlCeCommand("INSERT INTO meals(ID, RESIDENTID, MEALCODE, NUMBER_OF_MEALS, AMOUNT, DATETIME) VALUES(null ,?, ?, ?, ?.?, GETDATE())", con);
                comInsert.ExecuteNonQuery();
                con.Close();
            }catch (SqlCeException e){
                Console.WriteLine(e);
            }
        }

        public string getPreviousResIdBalance()
        {
            string residentId;
            try
            {
                SqlCeConnection con = new SqlCeConnection("Data Source=\\meals\\meals.sdf");
                con.Open();
                SqlCeCommand comSelect = new SqlCeCommand("SELECT LAST(RESIDENTID) FROM MEALS");
                SqlCeDataReader reader = comSelect.ExecuteReader();
                residentId = reader.GetString(0);
                reader.Close();
                return residentId;
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e);
                return "";
            }
        }

        public void saveValuesToTheDatabase(string mealCodeValue, string residentIdValue, string numberOfMealsValue, string amountValue){
            
            try
            {
                DateTime dateTime = DateTime.Today;
                SqlCeConnection con = new SqlCeConnection("Data Source=\\meals\\meals.sdf");
                con.Open();
                SqlCeParameter param1 = new SqlCeParameter();
                SqlCeParameter param2 = new SqlCeParameter();
                SqlCeParameter param3 = new SqlCeParameter();
                SqlCeParameter param4 = new SqlCeParameter();

                SqlCeCommand comInsert = new SqlCeCommand("INSERT INTO meals(RESIDENTID, MEALCODE, NUMBEROFMEALS, AMOUNT, DATETIME) VALUES(@residentIdValue, @mealCodeValue, @numberOfMealsValue, @amountValue, GETDATE())", con);

                param1.ParameterName = "@residentIdValue";
                param1.Value = residentIdValue;
                comInsert.Parameters.Add(param1);

                param2.ParameterName = "@mealCodeValue";
                param2.Value = mealCodeValue;
                comInsert.Parameters.Add(param2);

                param3.ParameterName = "@numberOfMealsValue";
                param3.Value = numberOfMealsValue;
                comInsert.Parameters.Add(param3);

                param4.ParameterName = "@amountValue";
                param4.Value = amountValue;
                comInsert.Parameters.Add(param4);

                comInsert.ExecuteNonQuery();
                con.Close();
            }catch (SqlCeException e){
                Console.WriteLine(e);
            }
        }

        public int getTotalDataCount(){
            int totalCount = -1;
            try{
                SqlCeConnection con = new SqlCeConnection("Data Source=\\meals\\meals.sdf");
                con.Open();
                SqlCeCommand comSelect = new SqlCeCommand("Select count(*) as totalValues from meals", con);
                totalCount = Convert.ToInt16(comSelect.ExecuteScalar());
                con.Close();
            }catch (SqlCeException e){
                Console.WriteLine("Exception:" + e.Message);
            }
            return totalCount;
        }

        public int getMaxId(){
            int maxId = -1;
            try{
                SqlCeConnection con = new SqlCeConnection("Data Source=\\meals\\meals.sdf");
                con.Open();
                SqlCeCommand comSelect = new SqlCeCommand("Select max(id) from meals", con);
                try
                {
                    maxId = Convert.ToInt16(comSelect.ExecuteScalar());
                }catch (InvalidCastException e){
                    con.Close();
                    return 0;
                }
                con.Close();
            }catch(SqlCeException e){
                Console.WriteLine("Exception:" + e.Message);
            }
            return maxId;
        }

        public int getMinId(){
            int minId = -1;
            try
            {
                SqlCeConnection con = new SqlCeConnection("Data Source=\\meals\\meals.sdf");
                con.Open();
                SqlCeCommand comSelect = new SqlCeCommand("Select min(id) from meals", con);
                try
                {
                    minId = Convert.ToInt16(comSelect.ExecuteScalar());
                }catch(InvalidCastException e){
                    con.Close();
                    return 0;
                }
                con.Close();
            }catch(SqlCeException e){
                Console.WriteLine("Exception:" + e.Message);
            }
            return minId;
        }

        public Meals getNextData(){
            
            Meals meal = null;
            try{
                SqlCeConnection con = new SqlCeConnection("Data Source=\\meals\\meals.sdf");
                con.Open();
                nextIndex = Convert.ToInt16(getNextIndex(currentIndex));
                int maxId = getMaxId();
                int minId = getMinId();
                SqlCeCommand comSelect;
                if((currentIndex == maxId) || (currentIndex == 0)){
                    currentDataNumber = 1;
                    comSelect = new SqlCeCommand("Select * from meals where id=@minId", con);
                    SqlCeParameter param1 = new SqlCeParameter();
                    param1.ParameterName = "@minId";
                    param1.Value = minId;
                    comSelect.Parameters.Add(param1);
                    currentIndex = minId;
                }else{
                    currentDataNumber++;
                    comSelect = new SqlCeCommand("Select * from meals where id=@nextIndex", con);
                    SqlCeParameter param1 = new SqlCeParameter();
                    param1.ParameterName = "@nextIndex";
                    param1.Value = nextIndex;
                    comSelect.Parameters.Add(param1);
                    currentIndex = nextIndex;
                }
                
                SqlCeDataReader reader = comSelect.ExecuteReader();
                int counter = 0;
                while(reader.Read()){
                    counter++;
                    long id = reader.GetInt64(0);
                    string residentId = reader.GetString(1);
                    string mealCode = reader.GetString(2);
                    string numberOfMeals = reader.GetString(3);
                    string amount = reader.GetString(4);
                    DateTime dateTime = reader.GetDateTime(5);
                    meal = new Meals(id, mealCode, residentId, numberOfMeals, amount, dateTime);
                }
                con.Close();
                if (counter == 0)
                {
                    return null;
                }
                
            }catch (SqlCeException e){
                Console.WriteLine("Exception:" + e.Message);
            }
            return meal;
        }

        public Meals getPreviousData(){
            
            Meals meal = null;
            try
            {
                SqlCeConnection con = new SqlCeConnection("Data Source=\\meals\\meals.sdf");
                con.Open();
                SqlCeCommand comSelect;
                previousIndex = Convert.ToInt16(getPreviousIndex(currentIndex));
                int minId = getMinId();
                int maxId = getMaxId();
                if (currentIndex == minId){
                    currentDataNumber = getTotalDataCount();
                    comSelect = new SqlCeCommand("Select * from meals where id=@maxId", con);
                    SqlCeParameter param1 = new SqlCeParameter();
                    param1.ParameterName = "@maxId";
                    param1.Value = maxId;
                    currentIndex = maxId;
                    comSelect.Parameters.Add(param1);
                }else{
                    currentDataNumber--;
                    comSelect = new SqlCeCommand("Select * from meals where id=@previousIndex", con);
                    SqlCeParameter param1 = new SqlCeParameter();
                    param1.ParameterName = "@previousIndex";
                    param1.Value = previousIndex;
                    currentIndex = previousIndex;
                    comSelect.Parameters.Add(param1);
                }
                
                SqlCeDataReader reader = comSelect.ExecuteReader();
                int counter = 0;
                while (reader.Read())
                {
                    counter++;
                    long id = reader.GetInt64(0);
                    string residentId = reader.GetString(1);
                    string mealCode = reader.GetString(2);
                    string numberOfMeals = reader.GetString(3);
                    string amount = reader.GetString(4);
                    DateTime transactionDate = reader.GetDateTime(5);
                    meal = new Meals(id, mealCode, residentId, numberOfMeals, amount, transactionDate);
                }
                con.Close();
                if (counter == 0)
                {
                    return null;
                }

                return meal;
            }catch (SqlCeException e){
                Console.WriteLine("Exception:" + e.Message);
                return null;
            }
        }

        public Meals getLastData(){

            int totalDataCount = getTotalDataCount();
            Meals meal = null;
            if (totalDataCount > 0)
            {
                try
                {
                    SqlCeConnection con = new SqlCeConnection("Data Source=\\meals\\meals.sdf");
                    con.Open();
                    SqlCeCommand comSelect;
                    int maxId = getMaxId();

                    comSelect = new SqlCeCommand("Select * from meals where id=@maxId", con);
                    SqlCeParameter param1 = new SqlCeParameter();
                    param1.ParameterName = "@maxId";
                    param1.Value = maxId;
                    comSelect.Parameters.Add(param1);

                    SqlCeDataReader reader = comSelect.ExecuteReader();
                    int counter = 0;
                    while (reader.Read())
                    {
                        counter++;
                        long id = reader.GetInt64(0);
                        string residentId = reader.GetString(1);
                        string mealCode = reader.GetString(2);
                        string numberOfMeals = reader.GetString(3);
                        string amount = reader.GetString(4);
                        DateTime transactionDate = reader.GetDateTime(5);
                        meal = new Meals(id, mealCode, residentId, numberOfMeals, amount, transactionDate);
                    }
                    con.Close();
                    if (counter == 0)
                    {
                        return null;
                    }

                    return meal;
                }
                catch (SqlCeException e)
                {
                    Console.WriteLine("Exception:" + e.Message);
                    return null;
                }
            }
            return null;
        }

        public void deleteCurrentIndex()
        {
            try
            {
                SqlCeConnection con = new SqlCeConnection("Data Source=\\meals\\meals.sdf");
                con.Open();
                SqlCeParameter param1 = new SqlCeParameter();
                SqlCeCommand comDelete = new SqlCeCommand("delete from meals where id=@cIndex", con);
                param1.ParameterName = "@cIndex";
                param1.Value = currentIndex;
                comDelete.Parameters.Add(param1);
                comDelete.ExecuteNonQuery();
                con.Close();
            }catch (SqlCeException e){
                Console.WriteLine("Exception:" + e.Message);
            }
        }

        public int getCurrentDataNumber()
        {
            return currentDataNumber;
        }

        public long getPreviousIndex(int cIndex)
        {
            try
            {
                SqlCeConnection con = new SqlCeConnection("Data Source=\\meals\\meals.sdf");
                con.Open();
                SqlCeCommand comSelect;

                comSelect = new SqlCeCommand("Select id from meals where id < @cIndex order by id desc", con);
                SqlCeParameter param1 = new SqlCeParameter();
                param1.ParameterName = "@cIndex";
                param1.Value = cIndex;
                comSelect.Parameters.Add(param1);

                SqlCeDataReader reader = comSelect.ExecuteReader();
                int counter = 0;
                long id = getMinId();
                while ((reader.Read())&&(counter < 1))
                {
                    counter++;
                    id = reader.GetInt64(0);
                }
                con.Close();
                if (counter == 0)
                {
                    return getMinId();
                }
                return id;
            }catch (SqlCeException e){
                Console.WriteLine("Exception:" + e.Message);
                return 0;
            }
        }

        public long getNextIndex(int cIndex)
        {
            try
            {
                SqlCeConnection con = new SqlCeConnection("Data Source=\\meals\\meals.sdf");
                con.Open();
                SqlCeCommand comSelect;
                comSelect = new SqlCeCommand("Select id from meals where id > @cIndex order by id asc", con);
                SqlCeParameter param1 = new SqlCeParameter();
                param1.ParameterName = "@cIndex";
                param1.Value = cIndex;
                comSelect.Parameters.Add(param1);
                SqlCeDataReader reader = comSelect.ExecuteReader();
                int counter = 0;
                long id = getMinId();
                while ((reader.Read())&&(counter < 1))
                {
                    counter++;
                    id = reader.GetInt64(0);
                }
                con.Close();
                if (counter == 0)
                {
                    return 0;
                }
                return id;
            }
            catch (SqlCeException e)
            {
                Console.WriteLine("Exception:" + e.Message);
                return 0;
            }
        }

        public void transferDataToFile()
        {
            deleteDataFile();
            createDataFile();
            try
            {
                SqlCeConnection con = new SqlCeConnection("Data Source=\\meals\\meals.sdf");
                con.Open();
                SqlCeCommand comSelect = new SqlCeCommand("Select * from meals", con);
                SqlCeDataReader reader = comSelect.ExecuteReader();
                string residentId;
                string mealCode;
                string numberOfMeals;
                string amount;
                DateTime dateTime;       
                
                StreamWriter sw = new StreamWriter("\\meals\\data\\barcode.upl");
                int counter = 0;
                while (reader.Read()){
                    counter = counter + 1;
                    residentId    =     reader.GetString(1);
                    mealCode      =     reader.GetString(2);
                    numberOfMeals =     reader.GetString(3);
                    amount        =     reader.GetString(4);
                    dateTime      =     reader.GetDateTime(5);

                    String line = residentId.ToString() + "," + mealCode.ToString() + "," + numberOfMeals.ToString() + "," +
                    amount.ToString() +"," + dateTime.Date.ToString("d") + "," + dateTime.Hour.ToString() + ":" +
                    dateTime.Minute.ToString() + ":" + dateTime.Second.ToString();
  
                    try {
				        //Pass the filepath and filename to the StreamWriter Constructor
				        //Write a line of text
				        sw.WriteLine(line);
				        //Close the file
				        
			        }catch(Exception e){
				        Console.WriteLine("Exception: " + e.Message);
			        }
                }
                sw.Close();
                reader.Close();
            }catch(SqlCeException e){
                Console.WriteLine(e);
            }
        }

        public void deleteDataFile(){
            File.Delete(sourceFile);
        }

        public void createDataFile(){
            File.Create(sourceFile);
        }

     }
}

   